Nonlinear Parameter Estimation Using Spreadsheet Software

Many fundamental processes in the natural and physical sciences exhibit substantial nonlinear relations among the component variables. Instructing students on how to identify parameters in models for such relations based on measured data, and providing a means to illustrate the impacts of specific components on the behavior of nonlinear relationships, is therefore a critical element of university science training. Commercially available spreadsheet software programs offer convenient and effective means for performing nonlinear parameter estimation. Among the advantages of the spreadsheet approach to nonlinear optimization and parameter estimation are its relative ease of use, general applicability, demonstration or reinforcement of least-squares principles, and the ability to interactively alter selected parameter values and immediately view the results via embedded graphics. In this paper we provide a brief discussion of nonlinear least-squares optimization, demonstrate its application within the context of some commercial spreadsheet software packages, and illustrate some of the capabilities of this approach using examples from our respective upper division Soil Physics lecture/laboratory courses. Our experiences indicate that students learn spreadsheet techniques more readily than often specialized mathematical or statistical programs, and that they provide a valuable tool in their future endeavors. MANY fundamental processes in soil physics exhibit highly nonlinear relationships among the component states and/or rates. This is true of many other disciplines as well. The ability to determine or estimate the value of component parameters is a critical aspect in the practical application of these relationships, and thus is an important aspect of university instruction. Effective parameter estimation typically requires use of numerical optimization techniques or specialized software (e.g., SAS, MathCAD, Mathematica)1. However, recent versions of many commercial spreadsheet software programs offer powerful and flexible means of performing nonlinear optimization. This offers a functional alternative to the use of more specialized mathematical or statistical software, while providing skills and abilities having potentially much wider application to students’ future endeavors. We have found through conversations with colleagues at other institutions that, although many of them use commercial spreadsheets extensively in teaching and research, the capacity to perform advanced nonlinear optimization within this context is not well recognized. A contributing factor may be the fact that explicit instructions or potential applications are generally not emphasized in the spreadsheet software manuals. We encourage our students to use spreadsheet software extensively. Specific applications include homework problem sets and a variety of laboratory exercises. Advantages of spreadsheets for these assignments include their general facility for data entry and/or transfer from other sources, data management, ease in performing simple or complex calculations, and often advanced graphics and reporting capabilities. These provide an effective tool for student use in many other venues, as well as for the immediate demands we place on them. Our objectives in this paper are to outline the methods of performing nonlinear parameter optimization using the commercial spreadsheet software with which we are most familiar, to illustrate the approach using a few specific examples from our courses, and to discuss advantages of this approach relative to the use of more specialized mathematical/statistical software packages. Our treatment of nonlinear optimization itself is intended to serve only as a brief overview of the subject, and of the relevant options commonly available in the spreadsheet environment. More exhaustive treatments are available elsewhere (e.g., Press et al., 1986; Luenberger, 1984).